import json
import sqlite3
import datetime
import os


class DB:
    def __init__(self):
        self.db_file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), f"../data/db.sqlite")
        self.conn = None

    def init(self):
        sql_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), f"../data/db_init.sql")
        with open(sql_path, 'r') as sql_file:
            sql_scripts = sql_file.read().split(';')
        try:
            for sql_script in sql_scripts:
                self.run_sql(sql_script)
                print(sql_script)
        except sqlite3.Error as e:
            print('init failed')
            raise e

    def open(self):
        self.conn = sqlite3.connect(self.db_file_path, timeout=10, isolation_level="IMMEDIATE")
        self.conn.execute('PRAGMA journal_mode=WAL')
        return self.conn.cursor()

    def close(self):
        if self.conn is not None:
            self.conn.close()

    def run_batch(self, sql, binding_list):
        try:
            cursor = self.open()
            cursor.executemany(sql, binding_list)
            self.conn.commit()
        finally:
            self.close()

    def run_sql(self, sql, bindings=None):
        try:
            cursor = self.open()
            if bindings is None:
                cursor.execute(sql)
            else:
                cursor.execute(sql, bindings)
            self.conn.commit()
        finally:
            self.close()

    def fetch_one(self, sql, bindings=None):
        try:
            cursor = self.open()
            if bindings is None:
                cursor.execute(sql)
            else:
                cursor.execute(sql, bindings)
            result = cursor.fetchone()
            return result
        finally:
            self.close()

    def fetch_all(self, sql, bindings=None):
        try:
            cursor = self.open()
            if bindings is None:
                cursor.execute(sql)
            else:
                cursor.execute(sql, bindings)
            result = cursor.fetchall()
            return result
        finally:
            self.close()

    def get_game_date(self, datetime_str=None):
        if datetime_str is None:
            dt = datetime.datetime.now()
        else:
            dt = datetime.datetime.strptime(datetime_str, '%Y-%m-%d %H:%M:%S')
        if dt.hour < 8:
            return (dt - datetime.timedelta(days=1)).strftime("%Y-%m-%d")
        else:
            return dt.strftime("%Y-%m-%d")

    def save_snapshot(self, user_id, data):
        snapshot = self.get_latest_snapshot(user_id)
        for key in data:
            snapshot[key] = data.get(key)
        shot_date = self.get_game_date()
        delete_sql = '''
            DELETE FROM snapshot where user_id = ? and shot_date = ?
            '''
        self.run_sql(delete_sql, (user_id, shot_date))

        insert_sql = """
            INSERT INTO snapshot (user_id, shot_date, shot_time, coin, p, energy, skill_sum, skills, backpack)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """
        bindings = (
            user_id,
            shot_date,
            datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            snapshot.get('coin', 0),
            snapshot.get('p', 0),
            snapshot.get('energy', 0),
            snapshot.get('skill_sum', 0),
            json.dumps(snapshot.get('skills', {})),
            json.dumps(snapshot.get('backpack', {}))
        )
        self.run_sql(insert_sql, bindings)

    def get_today_snapshot(self, user_id):
        sql = """
            SELECT coin, p, energy, skill_sum, skills, backpack, shot_date, shot_time
            FROM snapshot
            WHERE user_id = ? and shot_date = ?
            ORDER BY id DESC
            LIMIT 1
            """
        row = self.fetch_one(sql, (user_id, self.get_game_date()))
        if row is None:
            return {}
        return {
            'shot_date': row[6],
            'shot_time': row[7],
            "coin": row[0],
            "p": row[1],
            "energy": row[2],
            "skill_sum": row[3],
            "skills": json.loads(row[4]),
            "backpack": json.loads(row[5])
        }

    def get_latest_snapshot(self, user_id):
        sql = """
            SELECT coin, p, energy, skill_sum, skills, backpack, shot_date, shot_time
            FROM snapshot
            WHERE user_id = ?
            ORDER BY id DESC
            LIMIT 1
            """
        row = self.fetch_one(sql, (user_id,))
        if row is None:
            return {}
        return {
            'shot_date': row[6],
            'shot_time': row[7],
            "coin": row[0],
            "p": row[1],
            "energy": row[2],
            "skill_sum": row[3],
            "skills": json.loads(row[4]),
            "backpack": json.loads(row[5])
        }

    def get_snapshots(self, date=None):
        if date is None:
            date = self.get_game_date()
        sql = """
            SELECT coin, p, energy, skill_sum, skills, backpack, shot_date, shot_time, s.user_id
            FROM snapshot s
            INNER JOIN (
                SELECT user_id, MAX(shot_date) AS max_date
                FROM snapshot
                WHERE shot_date <= ?
                GROUP BY user_id
            ) latest
            ON s.user_id = latest.user_id
            AND s.shot_date = latest.max_date
            """
        rows = self.fetch_all(sql, (date,))
        result = {}
        for row in rows:
            user_id = str(row[8])
            result[user_id] = {
                'user_id': user_id,
                'shot_date': row[6],
                'shot_time': row[7],
                "coin": row[0],
                "p": row[1],
                "energy": row[2],
                "skill_sum": row[3],
                "skills": json.loads(row[4]),
                "backpack": json.loads(row[5])
            }
        return result

